﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using runtime = Microsoft.SqlServer.Dts.Runtime;
using runtimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using wrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;


namespace BILAB.Technical.History.PackageBuilder
{

    public class MulticastExport
    {
        private runtime.Package _p;
        private runtime.TaskHost _taskhost;
        private wrapper.MainPipe _mainpipe;
        
        private runtime.ConnectionManager _sourceCM;
        private wrapper.IDTSComponentMetaData100 _oledbDatasource;
        private wrapper.CManagedComponentWrapper _sourceInstance;

        private wrapper.IDTSComponentMetaData100 _multicast1;
        private wrapper.CManagedComponentWrapper _multicastInstance1;
        private wrapper.IDTSPath100  _path1;     

        private wrapper.IDTSComponentMetaData100 _mergeJoin;
        private wrapper.CManagedComponentWrapper _mergeJoinInstance;        

        private wrapper.IDTSComponentMetaData100 _pdtDatasource;
        private wrapper.CManagedComponentWrapper _pdtSourceInstance;

        wrapper.IDTSComponentMetaData100 _firstSort;
        wrapper.IDTSComponentMetaData100 _secondSort;

        wrapper.IDTSComponentMetaData100 _flatfileDestination;

        public void Export()
        {
            _p = new runtime.Package();
            _p.Name = "MulticastExport";
            _p.CreatorComputerName = System.Environment.MachineName;
            _p.CreatorName = System.Environment.UserName;

            this.addDataflow();
            this.addSourceCM();
            this.addSourceConnection();
            //this.addMulticast();
            //this.addPdtSource();
            //this.addSort();
            //this.addMerge();
            //this.addFactDestination();

            runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();
            a.SaveToXml("ZBILAB.History.dtsx", _p, null);
        }

        private void linkMulticastToFlatfile()
        {
            _mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(_multicast1.OutputCollection[1], _flatfileDestination.InputCollection[0]);

        }

        private void addFactDestination()
        {
            // Connection Manager
            runtime.ConnectionManager destinationCM = _p.Connections.Add("FLATFILE");

            destinationCM.Properties["ConnectionString"].SetValue(destinationCM, "facts.txt");
            destinationCM.Properties["Format"].SetValue(destinationCM, "Delimited");
            destinationCM.Properties["DataRowsToSkip"].SetValue(destinationCM, 0);
            destinationCM.Properties["ColumnNamesInFirstDataRow"].SetValue(destinationCM, false);
            destinationCM.Properties["Name"].SetValue(destinationCM, "FlatFileConnection");
            destinationCM.Properties["RowDelimiter"].SetValue(destinationCM, "\r\n");
            destinationCM.Properties["TextQualifier"].SetValue(destinationCM, "\"");

            _flatfileDestination  = _mainpipe.ComponentMetaDataCollection.New();
            _flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";
            wrapper.CManagedComponentWrapper _flatFileInstance = _flatfileDestination.Instantiate();
            _flatFileInstance.ProvideComponentProperties();

            _flatfileDestination.Name = "FLF Fact Destination";
           
            _flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID
                   = _p.Connections["FlatFileConnection"].ID;
            _flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager
                   = runtime.DtsConvert.GetExtendedInterface( _p.Connections["FlatFileConnection"]);

            _mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(_multicast1.OutputCollection[1],
                                    _flatfileDestination.InputCollection[0]);

            // Get column list of flatfiledestination
            wrapper.IDTSVirtualInputColumnCollection100 vColumns = 
                _flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

             runtimeWrapper.IDTSConnectionManagerFlatFile100 ff = destinationCM.InnerObject as runtimeWrapper.IDTSConnectionManagerFlatFile100;

             //        DtsConvert.GetExtendedInterface(cm);

            for (int cols = 0; cols < vColumns.Count; cols++)
                {
                    runtimeWrapper.IDTSConnectionManagerFlatFileColumn100 col = ff.Columns.Add();

                    // If this is the last column, set the delimiter to CRLF.
                    // Otherwise keep the delimiter as ",".
                    if (cols == vColumns.Count - 1)
                    {
                        col.ColumnDelimiter = "\r\n";
                    }
                    else
                    {
                        col.ColumnDelimiter = @",";
                    }

                    col.ColumnType = "Delimited";
                    col.DataType = vColumns[cols].DataType;
                    col.DataPrecision = vColumns[cols].Precision;
                    col.DataScale = vColumns[cols].Scale;
                    runtimeWrapper.IDTSName100 name = col as runtimeWrapper.IDTSName100;
                    name.Name = vColumns[cols].Name;
                }

            

            _flatFileInstance.AcquireConnections(null);
            _flatFileInstance.ReinitializeMetaData();
            foreach (wrapper.IDTSInputColumn100 col in _flatfileDestination.InputCollection[0].InputColumnCollection)
            {
                wrapper.IDTSExternalMetadataColumn100 exCol = _flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
                _flatFileInstance.MapInputColumn(_flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
            }
            _flatFileInstance.ReleaseConnections();


        }

        private void addMerge()
        {
            _mergeJoin = _mainpipe.ComponentMetaDataCollection.New();
            _mergeJoin.ComponentClassID = "DTSTransform.Merge";
            _mergeJoinInstance = _mergeJoin.Instantiate();

            _mergeJoinInstance.ProvideComponentProperties();
            _mergeJoin.Name = "MRG Join Fact with Ref";

            wrapper.IDTSPath100 fromMulticast = _mainpipe.PathCollection.New();
            fromMulticast.AttachPathAndPropagateNotifications(_firstSort.OutputCollection[0], _mergeJoin.InputCollection[0]);

            wrapper.IDTSPath100 fromPdt = _mainpipe.PathCollection.New();
            fromPdt.AttachPathAndPropagateNotifications(_secondSort.OutputCollection[0], _mergeJoin.InputCollection[1]);

            wrapper.IDTSVirtualInput100 vInput = this._mergeJoin.InputCollection[0].GetVirtualInput();
            _mergeJoinInstance.ReinitializeMetaData();

        }

        private void addSort()
        {
            // First sort
            _firstSort = _mainpipe.ComponentMetaDataCollection.New();
            _firstSort.ComponentClassID = "DTSTransform.Sort";
            wrapper.CManagedComponentWrapper firstSortInstance = _firstSort.Instantiate();
            firstSortInstance.ProvideComponentProperties();
            _firstSort.Name = "SRT First Sort";

            _mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(
               _multicast1.OutputCollection[0], _firstSort.InputCollection[0]);

            wrapper.IDTSVirtualInput100 vInput =  _firstSort.InputCollection[0].GetVirtualInput();
            wrapper.IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection["ProductKey"];
            wrapper.IDTSInputColumn100 col = firstSortInstance.SetUsageType(_firstSort.InputCollection[0].ID, vInput, vColumn.LineageID, wrapper.DTSUsageType.UT_READONLY);
            
            // As sort component has custom properties, Column has also custom properties for sort
            // see ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#sort
            firstSortInstance.SetInputColumnProperty(_firstSort.InputCollection[0].ID, col.ID, "NewSortKeyPosition", 1);
           



            // 2nd sort
            _secondSort = _mainpipe.ComponentMetaDataCollection.New();
            _secondSort.ComponentClassID = "DTSTransform.Sort";
            wrapper.CManagedComponentWrapper secondSortInstance = _secondSort.Instantiate();
            secondSortInstance.ProvideComponentProperties();
            _secondSort.Name = "SRT Second Sort";

            _mainpipe.PathCollection.New().AttachPathAndPropagateNotifications(
               _pdtDatasource.OutputCollection[0], _secondSort.InputCollection[0]);

            wrapper.IDTSVirtualInput100 vInput2 = _secondSort.InputCollection[0].GetVirtualInput();
            wrapper.IDTSVirtualInputColumn100 vColumn2 = vInput2.VirtualInputColumnCollection["ProductKey"];
            wrapper.IDTSInputColumn100 col2 = secondSortInstance.SetUsageType(_secondSort.InputCollection[0].ID, vInput2, vColumn2.LineageID, wrapper.DTSUsageType.UT_READONLY);
            // As sort component has custom properties, Column has also custom properties for sort
            // see ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/56f5df6a-56f6-43df-bca9-08476a3bd931.htm#sort
            secondSortInstance.SetInputColumnProperty(_secondSort.InputCollection[0].ID, col2.ID, "NewSortKeyPosition", 1);
            
        }

        private void addPdtSource()
        {
            _pdtDatasource = _mainpipe.ComponentMetaDataCollection.New();
            //_oledbDatasource.ComponentClassID = "DTSAdapter.OleDbSource";
            _pdtDatasource.ComponentClassID = "DTSAdapter.OLEDBSource";


            // Instanciate an OleDB source Instance for design time based on OleDB Source
            _pdtSourceInstance = _pdtDatasource.Instantiate();


            // ProvideComponentProperties acts as a kind of constructor and build input, output ...
            _pdtSourceInstance.ProvideComponentProperties();

            // Relink runtime oledb component with the package connection manager defined before
            _pdtDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = _sourceCM.ID;
            _pdtDatasource.RuntimeConnectionCollection[0].ConnectionManager
                            = runtime.DtsConvert.GetExtendedInterface(_sourceCM);

            /* Each dataflow component has properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/065a337e-9a0d-4a5e-aa45-87da58fa37c3.htm
             * 1 - Common Properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/51973502-5cc6-4125-9fce-e60fa1b7b796.htm
             * 2 - Custom Properties for oledb source :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/1361a37f-d647-416b-bb96-542b5258f38d.htm#oledb
             */

            _pdtSourceInstance.SetComponentProperty("AccessMode", 2);
            _pdtSourceInstance.SetComponentProperty("SqlCommand", "Select * from DimProduct");

            _pdtDatasource.Name = "OLE select Product";
            _pdtDatasource.Description = "OLE select Product";


            _pdtSourceInstance.AcquireConnections(null);
            _pdtSourceInstance.ReinitializeMetaData();
            _pdtSourceInstance.ReleaseConnections();

        }
        private void addMulticast()
        {
            /* Instanciate a multicast component */
            _multicast1 =  _mainpipe.ComponentMetaDataCollection.New();
            _multicast1.ComponentClassID = "DTSTransform.Multicast";           

            _multicastInstance1 = _multicast1.Instantiate();
            _multicastInstance1.ProvideComponentProperties();
            _multicast1.Name = "MLT FactInternetSales";

            /* Add paths and link oledb source to multicast component */
            _path1 = _mainpipe.PathCollection.New();
            _path1.AttachPathAndPropagateNotifications(_oledbDatasource.OutputCollection[0], _multicast1.InputCollection[0]);

            ////Simple test : addin a link to 2 row sample transformation 
            //    // 1st component
            //    wrapper.IDTSComponentMetaData100 number = _mainpipe.ComponentMetaDataCollection.New();
            //    number.ComponentClassID = "DTSTransform.RowSampling";
                
            //    wrapper.CManagedComponentWrapper numberInstance = number.Instantiate();
            //    numberInstance.ProvideComponentProperties();
            //    numberInstance.SetComponentProperty("SamplingValue", 10);
            //    number.Name = "SPL  first Instance";

            //    wrapper.IDTSPath100 path = _mainpipe.PathCollection.New();
            //    path.AttachPathAndPropagateNotifications(_multicast1.OutputCollection[0], number.InputCollection[0]);

            //    // 2nd compnent
            //    wrapper.IDTSComponentMetaData100 numberBis = _mainpipe.ComponentMetaDataCollection.New();
            //    numberBis.ComponentClassID = "DTSTransform.RowSampling";

            //    wrapper.CManagedComponentWrapper numberInstanceBis = numberBis.Instantiate();
            //    numberInstanceBis.ProvideComponentProperties();
            //    numberInstanceBis.SetComponentProperty("SamplingValue", 20);
            //    numberBis.Name = "SPL 2nd Instance";

            //    wrapper.IDTSPath100 pathBis = _mainpipe.PathCollection.New();
            //    pathBis.AttachPathAndPropagateNotifications(_multicast1.OutputCollection[1], numberBis.InputCollection[0]);

        }

        private void addSourceConnection()
        {
            _oledbDatasource = _mainpipe.ComponentMetaDataCollection.New();            
            _oledbDatasource.ComponentClassID = "DTSAdapter.OLEDBSource";
            

            // Instanciate an OleDB source Instance for design time based on OleDB Source
            _sourceInstance = _oledbDatasource.Instantiate();
            
            
            // ProvideComponentProperties acts as a kind of constructor and build input, output ...
            _sourceInstance.ProvideComponentProperties();

            // Relink runtime oledb component with the package connection manager defined before
            _oledbDatasource.RuntimeConnectionCollection[0].ConnectionManagerID = _sourceCM.ID;
            _oledbDatasource.RuntimeConnectionCollection[0].ConnectionManager 
                            = runtime.DtsConvert.GetExtendedInterface(_sourceCM);

            /* Each dataflow component has properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/065a337e-9a0d-4a5e-aa45-87da58fa37c3.htm
             * 1 - Common Properties :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/51973502-5cc6-4125-9fce-e60fa1b7b796.htm
             * 2 - Custom Properties for oledb source :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_5techref/html/1361a37f-d647-416b-bb96-542b5258f38d.htm#oledb
             */

            _sourceInstance.SetComponentProperty("AccessMode", 2);
            _sourceInstance.SetComponentProperty("SqlCommand", "Select * from FactInternetSales");
            
            _oledbDatasource.Name = "OLE select from Fact InternetSales";
            _oledbDatasource.Description = "OLE select from Fact InternetSales";
            

            _sourceInstance.AcquireConnections(null);
            _sourceInstance.ReinitializeMetaData();
            _sourceInstance.ReleaseConnections();

        }

        private void addSourceCM()
        {
            _sourceCM = _p.Connections.Add("OLEDB");
            _sourceCM.Name = "AdventureDWSource";
            _sourceCM.ConnectionString = @"Provider=SQLOLEDB.1;Password=myuser;Persist Security Info=True;User ID=myuser;Initial Catalog=AdventureWorksDW;Data Source=FREL20629";
        }
        private void addDataflow()
        {
            /*
             * Common properties of taskhost
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_1devconc/html/1b725922-ec59-4a47-9d55-e079463058f3.htm
             * 
             * Detail :
             * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/e39480e5-613f-4bf4-87b1-249942ed9a35.htm
             */

             /* Add Method
              * ms-help://MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10is_7ismrf/html/a0d4c1cf-d4a3-4297-8892-7b13f235fe9d.htm
              *  
              */

            _taskhost = (runtime.TaskHost)_p.Executables.Add("DTS.Pipeline");
            _taskhost.Name = "DFT History";
            _mainpipe = (wrapper.MainPipe)_taskhost.InnerObject;

            // Another example of taskhost creation
            //runtime.Executable e = _p.Executables.Add("Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask, Microsoft.SqlServer.BulkInsertTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            //runtime.TaskHost t = (runtime.TaskHost)e;
            //t.Name = "REH";         
        }


    }
}
